<?php
/**
 * 数据模型基类
 */
declare(strict_types=1);

namespace Libraries\Bases;

use Libraries\Connections\PDOConn;
use PDO;
use Swoole\Database\PDOProxy;
use Swoole\Database\PDOStatementProxy;
use Swoole\Coroutine;
use Throwable;
use ErrorException;

abstract class ModelBase
{
    protected $strTableName = ''; // 表名
    protected $strConnMaster = ''; // 主库连接名称
    protected $strConnSlave = ''; // 从库连接名称
    private $arrConn = []; // 保存了读写分离的连接
    private $arrLastSql = []; // 最近的SQL
    const MYSQL_DUPLICATE_ERROR = 1062;

    /**
     * 构造
     * @param string $strTableName 表名
     * @param string $strConnMaster 主库连接名
     * @param string $strConnSlave 从库连接名
     */
    public function __construct(string $strTableName = '', string $strConnMaster = '', string $strConnSlave = '')
    {
        strlen($strTableName) && $this->strTableName = $strTableName;
        $this->strConnMaster = (strlen($strConnMaster) ? $strConnMaster : PDOConn::MYSQL_MASTER);
        if (strlen($strConnSlave)) {
            $this->strConnSlave = $strConnSlave;
        } elseif ($this->strConnMaster !== PDOConn::MYSQL_MASTER) {
            $this->strConnSlave = $this->strConnMaster;
        } else {
            $this->strConnSlave = PDOConn::MYSQL_SLAVE;
        }
    }

    /**
     * 取最近的SQL
     * @return array
     */
    public function getLastSql(): array
    {
        return $this->arrLastSql;
    }

    /**
     * 开启事务
     * @return bool
     */
    public function begin(): bool
    {
        $objConn = $this->getConn($this->strConnMaster);
        if ($objConn && !$objConn->inTransaction()) {
            /* @var PDO $objConn */
            return $objConn->beginTransaction();
        }
        return false;
    }

    /**
     * 事务提交
     * @return bool
     */
    public function commit(): bool
    {
        $objConn = $this->getConn($this->strConnMaster);
        if ($objConn && $objConn->inTransaction()) {
            /* @var PDO $objConn */
            return $objConn->commit();
        }
        return false;
    }

    /**
     * 事务回滚
     * @return bool
     */
    public function rollBack(): bool
    {
        $objConn = $this->getConn($this->strConnMaster);
        if ($objConn && $objConn->inTransaction()) {
            /* @var PDO $objConn */
            return $objConn->rollBack();
        }
        return false;
    }

    /**
     * 按ID查询数据
     *
     * @param int $id
     * @param string $strCols
     * @return array
     * @throws ErrorException
     */
    public function getById(int $id, string $strCols = '*'): array
    {
        $strSql = 'SELECT ' . $strCols . ' FROM `' . $this->getTableName() . '` WHERE `id` = :id LIMIT 1';
        return $this->find($strSql, [':id' => $id]);
    }

    /**
     * 一次查询多个ID的数据
     *
     * @param array $ids
     * @param string $strCols
     * @return array
     * @throws ErrorException
     */
    public function getByIds(array $ids, string $strCols = '*'): array
    {
        $strIds = self::genInIdsSqlPart($ids);
        if (!$strIds) {
            return [];
        }
        $strSql = 'SELECT ' . $strCols . ' FROM `' . $this->getTableName() . '` WHERE `id` IN (' . $strIds . ')';
        return $this->findAll($strSql);
    }

    /**
     * 拼接多个ID
     * @param array $ids
     * @return string
     */
    private static function genInIdsSqlPart(array $ids): string
    {
        $strIds = '';
        $blnFirst = true;
        foreach ($ids as $id) {
            if ($blnFirst) {
                $blnFirst = false;
            } else {
                $strIds .= ',';
            }
            $strIds .= intval($id);
        }
        return $strIds;
    }

    /**
     * 按条件查询单条数据
     *
     * @param string $strSql
     * @param array $arrParam
     * @return array
     * @throws ErrorException
     */
    public function find(string $strSql, array $arrParam = []): array
    {
        $arrRe = [];
        $objStatement = $this->exec($strSql, $arrParam);
        if ($objStatement) {
            /* @var \PDOStatement $objStatement */
            $arrRe = $objStatement->fetch(PDO::FETCH_ASSOC);
            is_array($arrRe) || $arrRe = [];
        }
        return $arrRe;
    }

    /**
     * 按条件查询，返回所有数据
     *
     * @param string $strSql
     * @param array $arrParam
     * @return array
     * @throws ErrorException
     */
    public function findAll(string $strSql, array $arrParam = []): array
    {
        $arrRe = [];
        $objStatement = $this->exec($strSql, $arrParam);
        if ($objStatement) {
            /* @var \PDOStatement $objStatement */
            $arrRe = $objStatement->fetchAll(PDO::FETCH_ASSOC);
            is_array($arrRe) || $arrRe = [];
        }
        return $arrRe;
    }

    /**
     * 按分页取数据
     *
     * @param string $strSelect
     * @param string $strFromAndWhere
     * @param int $intPageSize
     * @param int $intCurrPageNum
     * @param array $arrFindParams
     * @param array $arrCountParams
     * @return array
     * @throws ErrorException
     */
    public function findByPage(
        string $strSelect = '*',
        string $strFromAndWhere = '',
        int $intPageSize = 20,
        int $intCurrPageNum = 1,
        array $arrFindParams = [],
        array $arrCountParams = []
    ): array {
        /* 参数验证 */
        $intPageSize <= 0 && $intPageSize = 20;
        $intCurrPageNum < 1 && $intCurrPageNum = 1;
        $strFromAndWhere || $strFromAndWhere = $this->getTableName();
        /* 返回的数据格式 */
        $arrRe = [
            'count' => 0, // 数据总个数
            'page_cnt' => 0, // 总页数
            'page_size' => $intPageSize, // 每页数据个数
            'page_curr' => $intCurrPageNum, // 当前第几页
            'data' => [], // 数据的数组
        ];
        /* 先查统计个数 */
        $strSql = 'SELECT COUNT(*) AS `count_total` FROM ' . $strFromAndWhere;
        $arrCount = $this->find($strSql, $arrCountParams ?: $arrFindParams);
        if ($arrCount && isset($arrCount['count_total'])) {
            $intCnt = intval($arrCount['count_total']);
            $arrRe['count'] = $intCnt;
            $arrRe['page_cnt'] = (int)ceil($intCnt / $intPageSize);
            /* 查询数据列表 */
            $intOffset = ($intCurrPageNum - 1) * $intPageSize;
            if ($intOffset < $arrCount) { // 个数必须大于0，且不超过分页的范围
                $strSql = 'SELECT ' . $strSelect . ' FROM ' . $strFromAndWhere;
                $strSql .= ' LIMIT ' . $intOffset . ', ' . $intPageSize;
                $arrRe['data'] = $this->findAll($strSql, $arrFindParams);
            }
        }
        return $arrRe;
    }

    /**
     * 向表中插入一条数据
     *
     * @param array $arrData
     * @param bool $blnIgnore
     * @return int|null
     * @throws ErrorException
     */
    public function insert(array $arrData, bool $blnIgnore = false): ?int
    {
        [$strCols, $strValues] = self::genInsertSqlPart($arrData);
        $strSql = 'INSERT' . ($blnIgnore ? ' IGNORE' : '') . ' INTO `';
        $strSql .= $this->getTableName() . '` (' . $strCols . ') ';
        $strSql .= 'VALUES (' . $strValues . ')';
        $objStatement = $this->exec($strSql, $arrData);
        /* @var \PDOStatement $objStatement */
        return $objStatement ? $objStatement->rowCount() : null;
    }
    
    /**
     * 生成Insert语句的SQL片段
     *
     * @param array $arrInsert
     * @return array
     */
    private static function genInsertSqlPart(array $arrInsert): array
    {
        $strCols = '';
        $strValues = '';
        $blnFirst = true;
        foreach ($arrInsert as $k => $_) {
            if ($blnFirst) {
                $blnFirst = false;
            } else {
                $strCols .= ',';
                $strValues .= ',';
            }
            $k = ltrim($k, ':');
            $strCols .= '`' . $k . '`';
            $strValues .= ':' . $k;
        }
        unset($_);
        return [$strCols, $strValues];
    }

    /**
     * 返回最后插入行的ID或序列值
     *
     * @return string|null
     */
    public function getLastInsertId(): ?string
    {
        $objConn = ($this->arrConn[$this->strConnMaster] ?? null);
        if (!$objConn) {
            return null;
        }
        return $objConn->lastInsertId();
    }

    /**
     * 批量插入数据，返回插入成功的数据条数
     *
     * @param array $arrData
     * @return int $intTotal 插入成功的数据条数
     * @throws ErrorException
     */
    public function batchInsert(array $arrData): int
    {
        if (!isset($arrData[0]) || !is_array($arrData[0])) {
            return 0;
        }
        [$strCols, $strValues] = self::genInsertSqlPart($arrData[0]);
        $strSql = 'INSERT IGNORE INTO `' . $this->getTableName() . '` (' . $strCols . ') ';
        $strSql .= 'VALUES (' . $strValues . ')';
        $objStatement = $this->exec($strSql, $arrData[0]);
        if (!$objStatement) {
            return 0;
        }
        /* @var \PDOStatement $objStatement */
        $intTotal = $objStatement->rowCount();
        array_shift($arrData);
        if (!is_array($arrData) || !count($arrData)) {
            return $intTotal;
        }
        try {
            foreach ($arrData as $item) {
                $objStatement->execute(self::genColonArray($item));
                $intTotal += $objStatement->rowCount();
            }
            return $intTotal;
        } catch (Throwable $objE) {
            $this->putConn();
            throw new ErrorException($objE->getMessage() . ', SQL => ' . $strSql);
        }
    }

    /**
     * 不重复时插入，重复就更新
     *
     * @param array $arrData
     * @return int|null
     * @throws ErrorException
     */
    public function duplicateInsert(array $arrData): ?int
    {
        [$arrData, $arrDataUpdate, $arrMerge] = self::genTransArray($arrData, $arrData);
        [$strCols, $strValues] = self::genInsertSqlPart($arrData);
        $strSql = 'INSERT INTO `' . $this->getTableName() . '` (' . $strCols . ') ';
        $strSql .= 'VALUES (' . $strValues . ') ';
        $strSql .= 'ON DUPLICATE KEY UPDATE ' . self::genConditionSqlPart($arrDataUpdate, ', ');
        $objStatement = $this->exec($strSql, $arrMerge, false);
        /* @var \PDOStatement $objStatement */
        return $objStatement ? $objStatement->rowCount() : null;
    }

    /**
     * 生成转换数据，以便数据绑定
     * @param array $arrays
     * @return array
     */
    private static function genTransArray(array ...$arrays): array
    {
        $arrRe = [];
        $arrMerge = [];
        $i = 0;
        $j = 0;
        $arrRe[0] = [];
        foreach ($arrays as $i => $arr) {
            $arrRe[$i] = [];
            foreach ($arr as $k => $v) {
                $kc = ':' . $k;
                while (key_exists($kc, $arrMerge)) {
                    $kc = ':' . $k . $j;
                    ++$j;
                }
                $arrRe[$i][$k] = $kc;
                $arrMerge[$kc] = $v;
            }
        }
        $arrRe[$i + 1] = $arrMerge;
        return $arrRe;
    }

    /**
     * 按条件更新数据
     *
     * @param array $arrData
     * @param array $arrCondition
     * @return int|null
     * @throws ErrorException
     */
    public function update(array $arrData, array $arrCondition): ?int
    {
        [$arrData, $arrCondition, $arrMerge] = self::genTransArray($arrData, $arrCondition);
        $strSql = 'UPDATE `' . $this->getTableName() . '` SET ' . self::genConditionSqlPart($arrData, ', ') . ' ';
        $strSql .= 'WHERE ' . self::genConditionSqlPart($arrCondition);
        $objStatement = $this->exec($strSql, $arrMerge, false);
        /* @var \PDOStatement $objStatement */
        return $objStatement ? $objStatement->rowCount() : null;
    }

    /**
     * 按条件删除数据
     *
     * @param array $arrCondition
     * @return int|null
     * @throws ErrorException
     */
    public function del(array $arrCondition): ?int
    {
        [$arrCondition, $arrMerge] = self::genTransArray($arrCondition);
        $strSql = 'DELETE FROM `' . $this->getTableName() . '` WHERE ' . self::genConditionSqlPart($arrCondition);
        $objStatement = $this->exec($strSql, $arrMerge, false);
        /* @var \PDOStatement $objStatement */
        return $objStatement ? $objStatement->rowCount() : null;
    }

    /**
     * 按ID删除数据
     *
     * @param int $intId
     * @return int|null
     * @throws ErrorException
     */
    public function delById(int $intId): ?int
    {
        return $this->del(['id' => $intId]);
    }

    /**
     * 按多个ID删除数据
     *
     * @param array $ids
     * @return int|null
     * @throws ErrorException
     */
    public function delByIds(array $ids): ?int
    {
        $strIds = self::genInIdsSqlPart($ids);
        if (!$strIds) {
            return 0;
        }
        $strSql = 'DELETE FROM `' . $this->getTableName() . '` WHERE `id` IN (' . $strIds . ')';
        $objStatement = $this->exec($strSql, [], false);
        /* @var \PDOStatement $objStatement */
        return $objStatement ? $objStatement->rowCount() : null;
    }

    /**
     * 生成以冒号为占位符的条件SQL片段
     * @param array $arr
     * @param string $strGlue
     * @return string
     */
    private static function genConditionSqlPart(array $arr, string $strGlue = ' AND '): string
    {
        $strRe = '';
        $blnFirst = true;
        foreach ($arr as $k => $kc) {
            if ($blnFirst) {
                $blnFirst = false;
            } else {
                $strRe .= $strGlue;
            }
            $strRe .= '`' . $k . '` = ' . $kc;
        }
        return $strRe;
    }

    /**
     * 获取表名
     * @return string
     */
    public function getTableName(): string
    {
        return $this->strTableName ?: $this->tableName ?? '';
    }

    /**
     * 执行原生SQL语句
     * @param string $strSql
     * @param array $arrParam
     * @param bool $blnColon 是否以冒号+key作为占位符
     * @return PDOStatementProxy|null
     * @throws ErrorException
     */
    public function exec(string $strSql, array $arrParam = [], bool $blnColon = true): ?PDOStatementProxy
    {
        $this->saveLastSql($strSql);
        $strSql = trim($strSql);
        /* 默认操作主库，当Select时，操作从库 */
        $strConnName = $this->strConnMaster;
        if (strtolower(substr($strSql, 0, 6)) == 'select') {
            $strConnName = $this->strConnSlave;
        }
        $objConn = $this->getConn($strConnName);
        if (!$objConn) {
            throw new ErrorException('Can NOT get DB connection !');
        }
        try {
            /* @var PDO $objConn */
            $objStatement = $objConn->prepare($strSql);
            $objStatement && $objStatement->execute($blnColon ? self::genColonArray($arrParam): $arrParam);
            return $objStatement;
        } catch (Throwable $objE) {
            if ($objE->getCode() == self::MYSQL_DUPLICATE_ERROR) {
                return null;
            } else {
                $this->putConn();
                throw new ErrorException($objE->getMessage() . ', SQL => ' . $strSql);
            }
        }
    }

    /**
     * 生成冒号+Key的数组
     * @param array $arr
     * @return array
     */
    private static function genColonArray(array $arr): array
    {
        $arrRe = [];
        foreach ($arr as $k => $v) {
            if ($k[0] !== ':') {
                $k = ':' . $k;
            }
            $arrRe[$k] = $v;
        }
        return $arrRe;
    }
    
    /**
     * 记录SQL
     * @param string $strSql
     * @return void
     */
    private function saveLastSql(string $strSql): void
    {
        array_push($this->arrLastSql, $strSql);
    }

    /**
     * 从连接池获取一个连接
     * @param string $strConnName
     * @return PDOProxy|null
     */
    private function getConn(string $strConnName = ''): ?PDOProxy
    {
        strlen($strConnName) || $strConnName = $this->strConnMaster;
        if (!isset($this->arrConn[$strConnName])) {
            $this->arrConn[$strConnName] = PDOConn::get($strConnName);
            Coroutine::defer(function () use ($strConnName) {
                $this->putConn($strConnName);
            });
        }
        return $this->arrConn[$strConnName];
    }

    /**
     * 归还连接，直接调用，可快速归还连接至连接池
     * @param string $strConnName
     * @return void
     */
    public function putConn(string $strConnName = ''): void
    {
        if (strlen($strConnName)) {
            if (isset($this->arrConn[$strConnName])) {
                PDOConn::put($this->arrConn[$strConnName], $strConnName);
                unset($this->arrConn[$strConnName]);
            }
        } else {
            foreach ($this->arrConn as $strConnName => $objConn) {
                PDOConn::put($objConn, $strConnName);
                unset($this->arrConn[$strConnName]);
            }
        }
    }
}
